package pg

import (
	"testing"

	"github.com/stretchr/testify/require"

	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	pgparser "github.com/bytebase/bytebase/backend/plugin/parser/pg"
	"github.com/bytebase/bytebase/backend/plugin/schema"
	"github.com/bytebase/bytebase/backend/store/model"
)

// TestAlterColumnAddIdentityParsing tests that ALTER COLUMN ADD IDENTITY is correctly parsed
func TestAlterColumnAddIdentityParsing(t *testing.T) {
	ddl := `ALTER TABLE orders ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;`

	parseResults, err := pgparser.ParsePostgreSQL(ddl)
	require.NoError(t, err)
	require.Len(t, parseResults, 1, "Should parse single statement")
	require.NotNil(t, parseResults[0].Tree, "Parse tree should exist")
}

// TestAlterColumnAddIdentityMetadata tests ALTER COLUMN ADD IDENTITY creates sequences
func TestAlterColumnAddIdentityMetadata(t *testing.T) {
	ddl := `
		CREATE TABLE products (
			id integer NOT NULL
		);

		ALTER TABLE products ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
			SEQUENCE NAME products_id_seq
			START WITH 1
			INCREMENT BY 1
		);
	`

	metadata, err := GetDatabaseMetadata(ddl)
	require.NoError(t, err)
	require.NotNil(t, metadata)

	require.NotEmpty(t, metadata.Schemas, "Should have schemas")
	schema := metadata.Schemas[0]

	require.Len(t, schema.Tables, 1, "Should have one table")
	table := schema.Tables[0]
	require.Equal(t, "products", table.Name)

	require.Len(t, table.Columns, 1, "Should have one column")
	column := table.Columns[0]
	require.Equal(t, "id", column.Name)
	require.Equal(t, storepb.ColumnMetadata_BY_DEFAULT, column.IdentityGeneration)

	require.Len(t, schema.Sequences, 1, "Should have one sequence")
	seq := schema.Sequences[0]
	require.Equal(t, "products_id_seq", seq.Name)
	require.Equal(t, "products", seq.OwnerTable)
	require.Equal(t, "id", seq.OwnerColumn)
}

// TestAlterColumnIdentityEquivalence tests that two equivalent schemas don't generate diffs
func TestAlterColumnIdentityEquivalence(t *testing.T) {
	// User pasted schema (ALTER COLUMN ADD IDENTITY format)
	userSchema := `
		CREATE TABLE "public"."orders" (
			"id" integer NOT NULL,
			"amount" numeric
		);

		ALTER TABLE "public"."orders" ALTER COLUMN "id" ADD GENERATED BY DEFAULT AS IDENTITY (
			SEQUENCE NAME "public"."orders_id_seq"
			START WITH 1
			INCREMENT BY 1
			MINVALUE 1
			MAXVALUE 2147483647
			CACHE 1
		);
	`

	// Database current state (inline IDENTITY format)
	dbSchema := `
		CREATE TABLE orders (
			id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			amount NUMERIC
		);
	`

	userMetadata, err := GetDatabaseMetadata(userSchema)
	require.NoError(t, err)
	require.NotNil(t, userMetadata)

	dbMetadata, err := GetDatabaseMetadata(dbSchema)
	require.NoError(t, err)
	require.NotNil(t, dbMetadata)

	// Simulate database sync adding LastValue
	for _, seq := range dbMetadata.Schemas[0].Sequences {
		seq.LastValue = "1"
	}

	// Compare schemas
	userDBSchema := model.NewDatabaseMetadata(userMetadata, nil, nil, storepb.Engine_POSTGRES, false)
	dbDBSchema := model.NewDatabaseMetadata(dbMetadata, nil, nil, storepb.Engine_POSTGRES, false)

	diff, err := schema.GetDatabaseSchemaDiff(storepb.Engine_POSTGRES, dbDBSchema, userDBSchema)
	require.NoError(t, err)

	// Key assertion: no sequence changes should be detected
	require.Empty(t, diff.SequenceChanges, "Should not detect sequence changes for equivalent schemas")
}

// TestAlterSequenceOwnedBy tests ALTER SEQUENCE OWNED BY parsing
func TestAlterSequenceOwnedBy(t *testing.T) {
	tests := []struct {
		name              string
		schema            string
		expectOwnerTable  string
		expectOwnerColumn string
	}{
		{
			name: "ALTER SEQUENCE with OWNED BY",
			schema: `
				CREATE SEQUENCE user_seq;
				CREATE TABLE users (id INT);
				ALTER SEQUENCE user_seq OWNED BY users.id;
			`,
			expectOwnerTable:  "users",
			expectOwnerColumn: "id",
		},
		{
			name: "CREATE SEQUENCE without OWNED BY",
			schema: `
				CREATE SEQUENCE user_seq;
			`,
			expectOwnerTable:  "",
			expectOwnerColumn: "",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			metadata, err := GetDatabaseMetadata(tt.schema)
			require.NoError(t, err)
			require.NotNil(t, metadata)

			require.NotEmpty(t, metadata.Schemas)
			require.NotEmpty(t, metadata.Schemas[0].Sequences)

			seq := metadata.Schemas[0].Sequences[0]
			require.Equal(t, tt.expectOwnerTable, seq.OwnerTable, "OwnerTable mismatch")
			require.Equal(t, tt.expectOwnerColumn, seq.OwnerColumn, "OwnerColumn mismatch")
		})
	}
}
